﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;

namespace ExpenseTracker.Platform
{
    public class DataHandler
    {
        private static DataTable expenseTypeDataTable = null;

        private static string connectionString = ConfigurationManager.ConnectionStrings["Expense_Tracker_Connection_String"].ConnectionString
                + ";Password= 'kumarshrestha'";

        #region methods -------------------------

        public DataTable GetData(int month_)
        {
            string query = String.Format("SELECT e.Id, e.Amount,  et.Type, e.Date, e.Comment" +
                        " FROM expense AS e INNER JOIN  ExpenseType AS et" +
                        " ON et.Id=e.ExpenseType"+
                        " where DATEPART(month, Date) = {0}", ++month_);
            DataTable dt = new DataTable();
            using (SqlCeConnection newConnection = new SqlCeConnection(connectionString))
            {
                newConnection.Open();
                using (SqlCeDataAdapter newAdapter = new SqlCeDataAdapter(query, newConnection))
                {
                    newAdapter.Fill(dt);  
                }
            }
            return dt;
        }

        public static DataTable GetExpenseTypes()
        {
            if (expenseTypeDataTable == null)
            {
                string query = "SELECT * FROM ExpenseType";
                expenseTypeDataTable = new DataTable();
                using (SqlCeConnection newConnection = new SqlCeConnection(connectionString))
                {
                    newConnection.Open();
                    using (SqlCeDataAdapter newAdapter = new SqlCeDataAdapter(query, newConnection))
                    {
                        newAdapter.Fill(expenseTypeDataTable);
                    }
                }
            }            
            return expenseTypeDataTable;
        }

        public static int GetExpenseId(string expenseType)
        {
            int expenseId = 0;
            if (expenseTypeDataTable == null)
            {
                GetExpenseTypes();
            }
            foreach (DataRow row in expenseTypeDataTable.Rows)
            {
                if (string.Equals((string)row["Type"], expenseType, StringComparison.OrdinalIgnoreCase))
                {
                    expenseId = (int)row["Id"];
                }
            }
            return expenseId;
        }

        public bool InsertData(List<ExpenseEntry> expenseEntriesToSave_)
        {
            using (SqlCeConnection newConnection = new SqlCeConnection(connectionString))
            {
                newConnection.Open();
                SqlCeCommand command = new SqlCeCommand("INSERT INTO expense (Amount, ExpenseType, Date, Comment)" +
                    " VALUES (@amount, @expenseType, @date, @comment)", newConnection);
                

                int count = 0;
                foreach (ExpenseEntry newExpense in expenseEntriesToSave_)
                {
                    SqlCeCommand checkIdCommand = new SqlCeCommand("SELECT COUNT(*) FROM expense WHERE Id=@id", newConnection);
                    checkIdCommand.Parameters.Add("@id", newExpense.Id);
                    if (Convert.ToInt32(checkIdCommand.ExecuteScalar()) >= 1)
                    {
                        Console.WriteLine("Expense entry already exists. Skipping");
                        continue;
                    }                    
                    command.Parameters.Add("@amount", newExpense.Amount);
                    command.Parameters.Add("@expenseType", newExpense.ExpenseType);
                    command.Parameters.Add("@date", newExpense.Date);
                    command.Parameters.Add("@comment", newExpense.Comment);
                    count += command.ExecuteNonQuery();
                }
                Console.WriteLine("number of lines affected " + count);
            }
            return true;
        }

        #endregion -----------------------------
    }
}
